﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC25BO
    /// </summary>
    public class VYC25BO:DBConnectionBO
    {
        public VYC25BO():base()
        {
            //
        }
        public List<ModelVYC25> GetAllList()
        {
            var list = new List<ModelVYC25>();
            const string query = "SELECT [Id],[IdTour],[GioiThieu],[Visa],[DieuKhoan],[MeoVat],[LuuY],[NgayCapNhat],[TrangThai] FROM [VYC-25]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC25();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdTour = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.GioiThieu = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.Visa = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.DieuKhoan = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.MeoVat = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.LuuY = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][7] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][7];
                model.TrangThai = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC25 model)
        {
            var query = "INSERT INTO [VYC-25]([IdTour],[GioiThieu],[Visa],[DieuKhoan],[MeoVat],[LuuY],[NgayCapNhat],[TrangThai]) VALUES (@idtour, @gioithieu, @visa, @dieukhoan, @meovat, @luuy, GETDATE(), @trangthai)";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = model.IdTour;
            sqlCommand.Parameters.Add("@gioithieu", SqlDbType.NText).Value = model.GioiThieu;
            sqlCommand.Parameters.Add("@visa", SqlDbType.NText).Value = model.Visa;
            sqlCommand.Parameters.Add("@dieukhoan", SqlDbType.NText).Value = model.DieuKhoan;
            sqlCommand.Parameters.Add("@meovat", SqlDbType.NText).Value = model.MeoVat;
            sqlCommand.Parameters.Add("@luuy", SqlDbType.NText).Value = model.LuuY;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC25 model)
        {
            var query = "UPDATE [VYC-25] SET " +
                        "[IdTour] = @idtour" +
                        "[GioiThieu] = @gioithieu" +
                        "[Visa] = @visa" +
                        "[DieuKhoan] = @dieukhoan" +
                        "[MeoVat] = @meovat" +
                        "[LuuY] = @luuy" +
                        "[NgayCapNhat] = GETDATE(), [TrangThai] = @trangthai" +
                        " WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = model.IdTour;
            sqlCommand.Parameters.Add("@gioithieu", SqlDbType.NText).Value = model.GioiThieu;
            sqlCommand.Parameters.Add("@visa", SqlDbType.NText).Value = model.Visa;
            sqlCommand.Parameters.Add("@dieukhoan", SqlDbType.NText).Value = model.DieuKhoan;
            sqlCommand.Parameters.Add("@meovat", SqlDbType.NText).Value = model.MeoVat;
            sqlCommand.Parameters.Add("@luuy", SqlDbType.NText).Value = model.LuuY;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC25 model)
        {
            var query = "DELETE FROM [VYC-25] WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;            
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ModelVYC25> GetById(int id)
        {
            var list = new List<ModelVYC25>();
            var query = "SELECT [Id],[IdTour],[GioiThieu],[Visa],[DieuKhoan],[MeoVat],[LuuY],[NgayCapNhat],[TrangThai] FROM [VYC-25] WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
            
            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC25();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.IdTour = reader["IdTour"].ToString();
                model.GioiThieu = reader["GioiThieu"].ToString();
                model.Visa = reader["Visa"].ToString();
                model.DieuKhoan = reader["DieuKhoan"].ToString();
                model.MeoVat = reader["MeoVat"].ToString();
                model.LuuY = reader["LuuY"].ToString();
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Get data by IdTour
        /// </summary>
        /// <param name="idtour"></param>
        /// <returns></returns>
        public List<ModelVYC25> GetByIdTour(string idtour)
        {
            var list = new List<ModelVYC25>();
            var query = "SELECT [Id],[IdTour],[GioiThieu],[Visa],[DieuKhoan],[MeoVat],[LuuY],[NgayCapNhat],[TrangThai] FROM [VYC-25] WHERE IdTour = @idtour";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = idtour;

            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC25();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.IdTour = reader["IdTour"].ToString();
                model.GioiThieu = reader["GioiThieu"].ToString();
                model.Visa = reader["Visa"].ToString();
                model.DieuKhoan = reader["DieuKhoan"].ToString();
                model.MeoVat = reader["MeoVat"].ToString();
                model.LuuY = reader["LuuY"].ToString();
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                list.Add(model);
            }
            return list;
        }
    }
}